const router = require('koa-router')()
const db = require('../db/initdb')
const path = require('path')
const fs = require('fs')
let userid;

router.prefix("/forum")

// 获取用户id
router.get("/", async (ctx, next) => {
    // console.log(ctx.query.userid)
    userid = ctx.query.userid;
    ctx.body = 'ok'
})

// 按作品发布时间降序,作者的用户类型升序,作品点赞数降序
// 用户表，作品表
// 作品id，作者id，作者昵称，作者头像，作品发布时间，作品标题，作品详情，作品地址，作品话题，收藏数，点赞数，评论数，被收藏，被点赞
// id, userid, name, avatar, date, title, detail, address, topic, collect, likenum, commentnum, isCollected, isLiked


// 初始化论坛列表
router.get("/getworklist", async (ctx, next) => {

    let sql = `select 
        hs.works.id,
        hs.works.userid, 
        hs.user.name, 
        hs.user.avatar, 
        hs.works.date, 
        hs.works.title,
        hs.works.detail, 
        hs.works.topic, 
        hs.works.collect, 
        hs.works.likenum, 
        hs.user.usertype
        from hs.works, hs.user
        where hs.works.userid = hs.user.id and hs.works.person = true and audit = true
        order by hs.works.date desc, hs.works.id desc, hs.user.usertype, hs.works.likenum desc`;
    let result = await db.query(sql)
    // console.log(result)
    // console.log(result.rows)
    let worklist = result.rows;


    // 每个作品的评论数量
    let commentSql = `select workid, COUNT(*) as commentnum from hs.comments group by workid`;
    let commentResult = await db.query(commentSql)
    // console.log(commentResult.rows)
    let commentlist = commentResult.rows;


    // 每个作品的图片/视频地址
    let addressSql = `select hs.picaddress.workid, hs.picaddress.address, hs.picaddress.perface
                    from hs.picaddress, hs.works
                    where hs.picaddress.workid = hs.works.id`;
    let addressResult = await db.query(addressSql)
    // console.log(addressResult.rows)
    let addresslist = addressResult.rows;


    // 每个作品被收藏
    let collectSql = `select id, collect from hs.star 
                    where hs.star.userid = $1 and collect = true`;
    let collectReault = await db.query(collectSql, [userid]);
    // console.log(collectReault.rows);
    let collectlist = collectReault.rows;


    // 每个作品被点赞
    let likeSql = `select id, likeo from hs.star 
                    where hs.star.userid = $1 and likeo = true`;
    let likeReault = await db.query(likeSql, [userid]);
    console.log(likeReault.rows);
    let likelist = likeReault.rows;


    // 将评论数添加到worklist中,作为list返回
    let list = [];
    worklist.map(item => {
        // 修改topic
        item.topic = item.topic.split(',');
        // console.log(item.topic)

        // 修改时间格式
        item.date = item.date.toLocaleDateString()
        // console.log(item.date.getFullYear() + '-' + (item.date.getMonth() + 1) + '-' + item.date.getDate())

        // 将评论数添加到worklist中,作为list返回
        // console.log(item.id)
        let id = item.id;
        let obj = commentlist.filter(item => item.workid == id)[0]
        if (obj) {
            item['commentnum'] = obj.commentnum;
        } else {
            item['commentnum'] = '0';
        }

        // 含有一个或多个图片,添加图片地址
        let arr = [];
        let add = addresslist.filter(item => item.workid == id);
        add.map(item => {
            arr.push(item.address)
        })
        item['address'] = arr;
        item['perface'] = add[0] ? add[0].perface : '';

        // 被收藏
        let isCollected = collectlist.filter(item => item.id == id)[0]
            ? collectlist.filter(item => item.id == id)[0].collect
            : false;
        item['isCollected'] = isCollected;


        // 被点赞
        let isLiked = likelist.filter(item => item.id == id)[0]
            ? likelist.filter(item => item.id == id)[0].likeo
            : false;
        item['isLiked'] = isLiked;


        list.push(item)
    })
    // console.log(list)

    // 返回数据
    ctx.body = list;
})

// 加载图片
router.get("/cover/:name", async (ctx, next) => {
    // console.log(ctx.params.name)
    let filePath = path.join(__dirname, `../public/images/${ctx.params.name}`);
    let fileContent = fs.readFileSync(filePath)
    ctx.set("Content-Type", "image/jpg")
    ctx.body = fileContent;
})

// 搜索关键字
router.get("/search", async (ctx, next) => {
    // console.log(ctx.query.info)
    let info = ctx.query.info;
    // let sql = `select * from hs.works where detail like '%${info}%' or title like '%${info}%' or topic like '%${info}%'`
    // let result = await db.query(sql)
    // console.log(result.rows)


    let sql = `select 
        hs.works.id,
        hs.works.userid, 
        hs.user.name, 
        hs.user.avatar, 
        hs.works.date, 
        hs.works.title,
        hs.works.detail, 
        hs.works.topic, 
        hs.works.collect, 
        hs.works.likenum, 
        hs.user.usertype
        from hs.works, hs.user
        where hs.works.userid = hs.user.id and hs.works.person = true and audit = true
        and (hs.works.detail like '%${info}%' or hs.works.title like '%${info}%' or hs.works.topic like '%${info}%')
        and hs.works.person = true and audit = true
        order by hs.works.date desc, hs.works.id desc, hs.user.usertype, hs.works.likenum desc`;
    let result = await db.query(sql)
    // console.log(result.rows)
    let worklist = result.rows;

    // 每个作品的评论数量
    let commentSql = `select workid, COUNT(*) as commentnum from hs.comments group by workid`;
    let commentResult = await db.query(commentSql)
    // console.log(commentResult.rows)
    let commentlist = commentResult.rows;

    // 每个作品的图片/视频地址
    let addressSql = `select hs.picaddress.workid, hs.picaddress.address, hs.picaddress.perface
                    from hs.picaddress, hs.works
                    where hs.picaddress.workid = hs.works.id`;
    let addressResult = await db.query(addressSql)
    // console.log(addressResult.rows)
    let addresslist = addressResult.rows;

    // 每个作品被收藏
    let collectSql = `select id, collect from hs.star 
        where hs.star.userid = $1 and collect = true`;
    let collectReault = await db.query(collectSql, [userid]);
    // console.log(collectReault.rows);
    let collectlist = collectReault.rows;

    // 每个作品被点赞
    let likeSql = `select id, likeo from hs.star 
        where hs.star.userid = $1 and likeo = true`;
    let likeReault = await db.query(likeSql, [userid]);
    // console.log(likeReault.rows);
    let likelist = likeReault.rows;

    // 将评论数添加到worklist中,作为list返回
    let list = [];
    worklist.map(item => {
        // 修改topic
        item.topic = item.topic.split(',');
        // 修改时间格式
        item.date = item.date.toLocaleDateString()
        // 将评论数添加到worklist中,作为list返回
        // console.log(item.id)
        let id = item.id;
        let obj = commentlist.filter(item => item.workid == id)[0]
        if (obj) {
            item['commentnum'] = obj.commentnum;
        } else {
            item['commentnum'] = '0';
        }
        // 含有一个或多个图片,添加图片地址
        let arr = [];
        let add = addresslist.filter(item => item.workid == id);
        add.map(item => {
            arr.push(item.address)
        })
        item['address'] = arr;
        item['perface'] = add[0] ? add[0].perface : '';
        // 被收藏
        let isCollected = collectlist.filter(item => item.id == id)[0]
            ? collectlist.filter(item => item.id == id)[0].collect
            : false;
        item['isCollected'] = isCollected;
        // 被点赞
        let isLiked = likelist.filter(item => item.id == id)[0]
            ? likelist.filter(item => item.id == id)[0].likeo
            : false;
        item['isLiked'] = isLiked;
        // 添加
        list.push(item)
    })
    ctx.body = list;
})

// 收藏
router.get("/collect", async (ctx, next) => {
    // console.log(ctx.query.workid)
    // console.log(ctx.query.userid)
    let workid = ctx.query.workid;
    let userid = ctx.query.userid;
    let isCollected;
    let sql = `select * from hs.star where id=$1 and userid=$2`;
    console.log(sql)
    let result = await db.query(sql, [workid, userid])
    // console.log(result)
    if (result.rowCount == 1) {
        isCollected = result.rows[0].collect;
        // 加减收藏数
        sql = isCollected ? `update hs.works set collect=collect-1 where id=$1`
            : `update hs.works set collect=collect+1 where id=$1`
        result = await db.query(sql, [workid])
        // 更改
        sql = `update hs.star set collect = $3,collectdate=$4 where id=$1 and userid=$2`
        result = await db.query(sql, [workid, userid, !isCollected, new Date()])
        // console.log(result);
        if (result.rowCount == 1) {
            ctx.body = true;
        }
    } else {
        // 加减收藏数
        sql = isCollected ? `update hs.works set collect=collect-1 where id=$1`
            : `update hs.works set collect=collect+1 where id=$1`
        result = await db.query(sql, [workid])
        // 添加
        sql = `insert into hs.star (id, userid, collect,collectdate) values ($1, $2, true,$3)`;
        result = await db.query(sql, [workid, userid, new Date()])
        // console.log(result)
        if (result.rowCount == 1) {
            ctx.body = true;
        }
    }
})

// 点赞
router.get("/like", async (ctx, next) => {
    let workid = ctx.query.workid;
    let userid = ctx.query.userid;
    // console.log(userid + "点赞了" + workid)
    let isLiked;
    let sql = `select * from hs.star where id = $1 and userid = $2`;
    let result = await db.query(sql, [workid, userid])
    // console.log(result)
    if (result.rowCount == 1) {
        isLiked = result.rows[0].likeo;
        // 加减点赞数
        sql = isLiked ? `update hs.works set likenum=likenum-1 where id=$1`
            : `update hs.works set likenum=likenum+1 where id=$1`
        result = await db.query(sql, [workid])
        // 更改
        sql = `update hs.star set likeo = $3,likedate=$4 where id=$1 and userid=$2`
        result = await db.query(sql, [workid, userid, !isLiked, new Date()])
        // console.log(result);
        if (result.rowCount == 1) {
            ctx.body = true;
        }
    } else {
        // 加减点赞数
        sql = isLiked ? `update hs.works set likenum=likenum-1 where id=$1`
            : `update hs.works set likenum=likenum+1 where id=$1`
        result = await db.query(sql, [workid])
        // 添加
        sql = `insert into hs.star (id, userid, likeo,likedate) values ($1, $2, true,$3)`;
        result = await db.query(sql, [workid, userid, new Date()])
        // console.log(result)
        if (result.rowCount == 1) {
            ctx.body = true;
        }
    }
})

// 跳转作品详情页
router.get("/detail", async (ctx, next) => {
    let workid = ctx.query.workid;

    // 作品id，作者id，发布时间，定位，文案内容，作品类型，图片地址/视频地址，点赞数，收藏数，{作者昵称，作者头像，被关注}，被收藏，被点赞
    // workid, userid, date, local, detail, picture, address/perface, collect, likenum, {name, avatar, isConcerned}, isCollected, isLiked


    let sql = `select id, userid, date, local, detail, picture, collect, likenum
            from hs.works where id = $1`;
    let result = await db.query(sql, [workid]);
    // console.log(result.rows[0])
    let workObj = result.rows[0];

    // 修改时间
    workObj.date = workObj.date.toLocaleDateString();

    // 作者详情
    sql = `select name, avatar from hs.user where id=$1`;
    result = await db.query(sql, [workObj.userid]);
    // console.log(result.rows[0])
    workObj['user'] = result.rows[0];

    // 被关注
    sql = `select * from hs.concern where userid=$1 and concernid=$2`;
    result = await db.query(sql, [userid, workObj.userid]);
    // console.log(result)
    if (result.rowCount == 1) {
        workObj.user['isConcerned'] = true;
    } else {
        workObj.user['isConcerned'] = false;
    }

    // 图片/视频
    sql = `select * from hs.picaddress where workid=$1`
    result = await db.query(sql, [workid])
    // console.log(result.rows)
    if (workObj.picture) {
        // 图片地址
        workObj['addressList'] = [];
        result.rows.map(item => {
            // console.log(item.address)
            workObj.addressList.push(item.address)
        })
    } else {
        if (result.rows[0]) {
            // 视频
            workObj['address'] = result.rows[0].address; //视频地址
            workObj['perface'] = result.rows[0].perface; //封面地址
        } else {
            // 纯文字
            workObj['address'] = []
        }
    }

    // 被收藏、被点赞
    sql = `select * from hs.star where id=$1 and userid=$2`
    result = await db.query(sql, [workid, userid])
    // console.log(result.rows)
    workObj['isCollected'] = result.rows[0] ? result.rows[0].collect : false;
    workObj['isLiked'] = result.rows[0] ? result.rows[0].likeo : false;
    // console.log(workObj);

    ctx.body = workObj;
})

// 详情页加载图片
router.get("/detail/image/:name", async (ctx, next) => {
    // console.log(ctx.params.name)
    let filePath = path.join(__dirname, `../public/images/${ctx.params.name}`);
    let fileContent = fs.readFileSync(filePath)
    ctx.set("Content-Type", "image/jpg")
    ctx.body = fileContent;
})

// 详情页加载视频
router.get("/detail/video/:name", async (ctx, next) => {
    let filePath = path.join(__dirname, `../public/images/${ctx.params.name}`);
    let fileContent = fs.readFileSync(filePath)
    // console.log(fileContent)
    ctx.body = fileContent;
})

// 详情页评论列表
router.get("/detail/comment", async (ctx, next) => {
    console.log("workid：" + ctx.query.workid)
    let workid = ctx.query.workid;
    let comment;

    // 评论id，评论人id，评论人昵称，评论人头像，评论内容，[{回复id，回复昵称，回复内容}, ...]
    // commentid, userid, name, avatar, commments, reply:[{replyid, name, comments}, ...]

    let sql = `select hs.comments.id as commentid, hs.user.id as userid, hs.user.name, hs.user.avatar, hs.comments.comments
            from hs.comments, hs.user
            where workid=$1 and new=false
            and hs.user.id = hs.comments.userid order by hs.comments.date DESC`;
    let result = await db.query(sql, [workid])
    // console.log(result.rows)
    comment = result.rows;

    // 获取回复
    sql = `select hs.comments.id, hs.user.name, hs.comments.comments, hs.comments.return
        from hs.comments, hs.user
        where workid=$1 and new=true
        and hs.user.id = hs.comments.userid order by hs.comments.date DESC`;
    result = await db.query(sql, [workid])
    // console.log(result.rows)
    replylist = result.rows;

    comment.map(item => {
        // console.log(item.commentid)
        item['reply'] = [];
        let commentid = item.commentid;
        // console.log(replylist.filter(item => item.return == commentid))
        let reply = replylist.filter(item => item.return == commentid)
        item.reply = [...reply];
    })
    // console.log(comment)

    ctx.body = comment;
})

// 回复评论
router.post("/detail/reply", async (ctx, next) => {
    // console.log(ctx.query.workid)
    let workid = ctx.query.workid;
    // console.log(ctx.request.body)
    let obj = ctx.request.body;
    let sql = 'insert into hs.comments (workid, userid, comments, return, new,date) values ($1, $2, $3, $4, true,$5)';
    let result = await db.query(sql, [workid, obj.userid, obj.comments, obj.return, new Date()]);
    // console.log(result)
    if (result.rowCount == 1) {
        ctx.body = 'ok'
    }
})

// 新增评论
router.post("/detail/comment", async (ctx, next) => {
    let workid = ctx.query.workid;
    let obj = ctx.request.body;
    let sql = `insert into hs.comments (workid, userid, comments, new,date) values ($1, $2, $3, false,$4)`;
    let result = await db.query(sql, [workid, obj.userid, obj.comments, new Date()])
    // console.log(result)
    if (result.rowCount == 1) {
        ctx.body = 'ok'
    }
})

// // 关注
// router.post("/detail/concern", async (ctx, next) => {
//     let userid = ctx.request.body.userid;
//     let concernid = ctx.request.body.concernid;
//     console.log("userid:" + userid + ",concernid:" + concernid);
//     let sql = `insert into hs.concern (userid, concernid) values ($1, $2)`;
//     let result = await db.query(sql, [userid, concernid])
//     console.log(result)
//     if (result.rowCount == 1) {
//         ctx.body = 'ok'
//     }
// })

module.exports = router
